package com.bytenebula.asteroid.service.impl;

import com.bytenebula.asteroid.DynamicDataSource;
import com.bytenebula.asteroid.entity.MetaColumn;
import com.bytenebula.asteroid.entity.MetaTable;
import com.bytenebula.asteroid.service.ExtractService;
import org.springframework.stereotype.Component;

import java.lang.reflect.InvocationTargetException;
import java.net.MalformedURLException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;

/**
 * 定义MSSQL数据库抽取器
 *
 * @author patrick
 * @since 2024-09-23
 */
@Component
public class MssqlExtractService extends ExtractServiceImpl {

    /**
     * 根据数据库名称抽取数据库里所有的表信息
     *
     * @param db_name 待抽取数据库名称
     * @return 返回数据表列表
     */
    @Override
    public List<MetaTable> extractTables(String db_name) {
        String sql_tpl = "SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE FROM information_schema.tables WHERE TABLE_CATALOG='%s' ORDER BY TABLE_NAME";
        return super.fetchTables(db_name, String.format(sql_tpl, db_name));
    }

    /**
     * 根据数据表名称抽取列信息
     *
     * @param table_name 待抽取数据表名称
     * @return 返回数据列列表
     */
    @Override
    public List<MetaColumn> extractColumns(String db_name, String table_name) {
        String sql_tpl = """
                SELECT col.*,
                	   IIF(CONSTRAINT_NAME IS NOT NULL, 'PRI', NULL) AS COLUMN_KEY,
                	   '' AS COLUMN_COMMENT
                FROM information_schema.columns col
                LEFT OUTER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE colkey
                    ON  (col.TABLE_CATALOG = colkey.TABLE_CATALOG
                        AND col.TABLE_SCHEMA  = colkey.TABLE_SCHEMA
                        AND col.TABLE_NAME  = colkey.TABLE_NAME
                        AND col.COLUMN_NAME = colkey.COLUMN_NAME)
                WHERE col.TABLE_CATALOG='%s' AND col.TABLE_NAME='%s'
                ORDER BY ORDINAL_POSITION""";
        return super.fetchColumns(db_name, String.format(sql_tpl, db_name, table_name));
    }
}
